package com.pwt.repository;

import com.pwt.repository.base.BaseRepository;
import com.pwt.entity.Menu;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Set;

/**
 * Menu Repository层
 *
 * @author auto generated
 * @date 2021-08-30 11:15:30
 */
public interface MenuRepository extends BaseRepository<Menu, String> {

    @Query(nativeQuery = true,value = "select m.* from [menu] m left join [role_menu] rm on m.id = rm.menu_id left join [user_role] ur on rm.role_id = ur.role_id left join [user] u on ur.user_id = u.id where u.username = ?1")
    Set<Menu> findByUsername(String username);

    @Query("select count(1) from Menu where permission = ?1")
    int countByPermission(String permission);

    @Query("select count(1) from Menu where id = ?1")
    int countById(String parentId);

    @Query("select m from Menu m where m.level = 0 order by m.sortNumber")
    List<Menu> findParents();

    @Query(value = "SELECT DISTINCT " +
            "mmm.permission + '::' + mm.permission + '::' + m.permission " +
            "FROM " +
            "menu AS m " +
            "LEFT JOIN menu AS mm ON m.parent_id = mm.id " +
            "LEFT JOIN menu AS mmm ON mm.parent_id = mmm.id " +
            "LEFT JOIN role_menu AS rm ON rm.menu_id = m.id " +
            "LEFT JOIN user_role AS ur ON ur.role_id = rm.role_id " +
            "LEFT JOIN [user] AS u ON ur.user_id = u.id " +
            "WHERE " +
            "u.username = ?1",nativeQuery = true)
    Set<String> findGrantedMenuByUsername(String username);

    @Query("select count(1) from Menu where parentId = ?1")
    int countByParentId(String id);
}
